﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.IO;
using System.Reflection;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace LearnNPOI
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            string excelpath = @"C:\Users\Miche\Desktop\末尾淘汰分析.xlsx";
            //string excelpath = @"C:\Users\Miche\Desktop\New XLS 工作表.xls";
            var dt = ExcelToDataTable(excelpath, true);
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Console.Write(dt.Rows[i][j].ToString());
                }

                Console.WriteLine();
            }

            //var r = dt.ToDataList<tempmodel>();
            //var json = JsonConvert.SerializeObject(r);
            //Console.WriteLine(json);
            //Console.WriteLine("-----------------------------");

            //FileStream fs = new FileStream(excelpath, FileMode.Open, FileAccess.Read);
            //var temp = ExcelHelper.GetList<tempmodel>(fs, 2);
            //var json1 = JsonConvert.SerializeObject(temp);
            //Console.WriteLine(json1);
            //Console.WriteLine("ok");

            //var result = WeihanLi.Npoi.ExcelHelper.ToEntityList<tempmodel>(excelpath, 0);
            //Console.WriteLine(JsonConvert.SerializeObject(result));

            //var result = WeihanLi.Npoi.ExcelHelper.ToEntityList<T2>(excelpath, 2);
            //Console.WriteLine(JsonConvert.SerializeObject(result));

            //FileStream fs = new FileStream(excelpath, FileMode.Open, FileAccess.Read);
            //var result = WeihanLi.Npoi.ExcelHelper.ToEntityList<T2>(fs, WeihanLi.Npoi.ExcelFormat.Xlsx, 2);
            //Console.WriteLine(JsonConvert.SerializeObject(result));

            Console.ReadKey();
        }

        /// <summary>  
        /// 将excel导入到datatable  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            //if (cell.StringCellValue != null)
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }
    }

    class tempmodel
    {
        [Column(Order = 0)]
        public string 一 { get; set; }

        [Column(Order = 1)]
        public string 二 { get; set; }

        [Column(Order = 2)]
        public string 三 { get; set; }

        [Column(Order = 3)]
        public int 四 { get; set; }
    }

    public static class DataTableEx
    {

        /// <summary>
        /// DataTable转成List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ToDataList<T>(this DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }

    }

    public class T2
    {
        /// <summary>
        /// Subbranch Id
        /// </summary>
        public long SubbranchId { get; set; }

        /// <summary>
        /// Subbranch Name
        /// </summary>
        public string SubbranchName { get; set; }

        /// <summary>
        /// 计划时间
        /// </summary>
        public DateTime ScheduleTime { get; set; }

        /// <summary>
        /// 时长(分钟)
        /// </summary>
        public uint Duration { get; set; }

        /// <summary>
        /// 教练Id
        /// </summary>
        public long TrainerId { get; set; }

        /// <summary>
        /// 教练姓名
        /// </summary>
        public string TrainerName { get; set; }

        /// <summary>
        /// 课程种类
        /// 飞轮 = 0;瑜伽 = 1;团操 = 2
        /// </summary>
        public GTLessonType LessonType { get; set; }

        /// <summary>
        /// 课程名称
        /// </summary>
        public string LessonName { get; set; }

        /// <summary>
        /// 是否免费课
        /// </summary>
        public bool IsFree { get; set; }

        /// <summary>
        /// 人数上限
        /// </summary>
        public uint MaxLimit { get; set; }

        /// <summary>
        /// 可以预约的开始时间
        /// null为不限制
        /// </summary>
        public DateTime? ValidAappointmentStartDate { get; set; }

        /// <summary>
        /// 可以预约的结束时间
        /// null为不限制
        /// </summary>
        public DateTime? ValidAappointmentEndDate { get; set; }

        /// <summary>
        /// 可以确认签课的开始时间
        /// null为不限制
        /// </summary>
        public DateTime? ValidConfirmStartDate { get; set; }

        /// <summary>
        /// 可以确认签课的结束时间
        /// null为不限制
        /// </summary>
        public DateTime? ValidConfirmEndDate { get; set; }

        /// <summary>
        /// 课程标签
        /// </summary>
        public string Tag { get; set; }

        /// <summary>
        /// 地点
        /// </summary>
        public string Site { get; set; }

        public enum GTLessonType
        {
            飞轮 = 0,
            瑜伽 = 1,
            团操 = 2
        }
    }
}
